﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace Kalficom.Libraies
{
    class db
    {
        private SqlConnection cnn = new SqlConnection(string.Format("SERVER='{0}';DATABASE='{1}';USER=kalficom;PASSWORD=k@lf!c0m", conf.Server, conf.Database));
        

        internal void FillDataSet(string SqlQuery, DataTable Table, bool ClearData = true, string Processing = null, LoadOption Option = LoadOption.PreserveChanges)
        {
            using (new WaitCursor())
            {
                using (SqlDataAdapter adp = new SqlDataAdapter(SqlQuery, cnn))
                {
                    if (cnn.State == ConnectionState.Open)
                        cnn.Close();
                    try
                    {
                        cnn.Open();
                        SqlCommandBuilder Builder = new SqlCommandBuilder(adp);
                        adp.FillLoadOption = Option;
                        if (ClearData)
                            Table.Clear();
                        adp.Fill(Table);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }
        }

        internal Boolean OpenConnection()
        {
            using (new WaitCursor())
            {
                if (cnn.State == ConnectionState.Open) { cnn.Close(); }
                if (cnn.State == ConnectionState.Broken) { cnn.Close(); }
                try
                {
                    cnn.Open();
                    return true;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
        }

        internal void CommandBuilder(String Sql, DataRow[] Rows, bool isContinueUpdateOnError = true)
        {
            using (new WaitCursor())
            {
                using (SqlDataAdapter adp = new SqlDataAdapter(Sql, cnn))
                {
                    SqlCommandBuilder builder = new SqlCommandBuilder(adp);
                    //adp.ContinueUpdateOnError = isContinueUpdateOnError;
                    try
                    {
                        adp.Update(Rows);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }
        }

        internal void CommandBuilder(String Sql, DataTable tbl, bool isContinueUpdateOnError = true)
        {
            using (new WaitCursor())
            {
                using (SqlDataAdapter adp = new SqlDataAdapter(Sql, cnn))
                {
                    SqlCommandBuilder builder = new SqlCommandBuilder(adp);
                    //adp.ContinueUpdateOnError = isContinueUpdateOnError;
                    try
                    {
                        adp.Update(tbl);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }
        }

        internal String ExecuteScalar(String SqlString)
        {
            using (new WaitCursor())
            {
                if (OpenConnection() == false)
                {
                    return "";
                }
                using (SqlCommand cmd = new SqlCommand(SqlString, cnn))
                {
                    if (cmd.ExecuteScalar() == DBNull.Value)
                    {
                        return "";
                    }
                    if (cmd.ExecuteScalar() == null)
                    {
                        return "";
                    }
                    return Convert.ToString(cmd.ExecuteScalar());
                }
            }
        }

        internal void ExecuteNonQuery(String SqlString)
        {
            using (new WaitCursor())
            {
                if (OpenConnection() == false) { return; }
                using (SqlCommand cmd = new SqlCommand(SqlString, cnn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        internal String SqlString(Object SqlText)
        {
            if (SqlText == null || SqlText == DBNull.Value)
            { return "null"; }
            else if (SqlText is String)
            {
                if (SqlText == null) { return "null"; }
                return String.Format("'{0}'", SqlText.ToString().Replace("'", "''"));
            }
            else
            { return String.Format("'{0}'", SqlText.ToString().Replace("'", "''")); }
        }

        internal String GetDate()
        {
            if (OpenConnection() == false) { return null; }
            SqlCommand cmd = new SqlCommand("SELECT GetDate()", cnn);
            return Convert.ToString(cmd.ExecuteScalar());
        }

    }
}
